---indicator procedures
--PI indicators
USE [PI]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Stefania Barbu>
-- Create date: <2018-11-01>
-- Description:	<Gets indicators for GA database>
-- =============================================
create PROCEDURE [dbo].[getProductInvolvementIndicatorData]
	-- Add the parameters for the stored procedure here
AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	select c.*, i.Name,i.Symbol IndicatorSymbol from Categories c
	join Indicators i on c.IndicatorId = i.Id
	where i.Platform_Status >0
	order by 
	i.Name, c.Symbol
END
--SP Indicators
USE [PI]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Stanescu Mihai>
-- Create date: <02-11-2018>
-- Description:	<Get Solution Indicators framework>
-- =============================================
create PROCEDURE [dbo].[getSustainableProductsIndicatorData] 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT c.*,i.Symbol as IndicatorSymbol, i.Name from Categories_Solutions c
	join Indicators_Solutions i on c.IndicatorId = i.Id
	where i.Platform_Status > 0
	order by i.Name, c.Symbol
END

--company data procedures
--PI data
USE [PI]
GO
/****** Object:  StoredProcedure [dbo].[researchedCompaniesSnapshot]    Script Date: 12/7/2018 12:39:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[researchedCompaniesSnapshot]
	-- Add the parameters for the stored procedure here
	@GetAll  Bit = NULL
AS
	if @GetAll is null 
	begin
		Set @Getall=1
	end

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	select * from traditionalDailyResearchData s with (nolock)
	order by CompanyId, IndicatorSymbol, CategorySymbol

end

--SP data
USE [PI]
GO
/****** Object:  StoredProcedure [dbo].[researchedCompaniesSnapshot_Solutions]    Script Date: 12/7/2018 12:41:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Andrei Ioan
-- Create date: 08/08/2018
-- Description:	Procedure to create the snapshot for solutions research
-- =============================================
ALTER PROCEDURE  [dbo].[researchedCompaniesSnapshot_Solutions] 
	-- Add the parameters for the stored procedure here
	@GetAll  Bit = NULL
AS
	if @GetAll is null 
	begin
		Set @Getall=1
	end

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

	select * from solutionsDailyResearchData s with (nolock)
	order by CompanyId, IndicatorSymbol, CategorySymbol

END

---All companies procedures
--PI
USE [PI]
GO
/****** Object:  StoredProcedure [dbo].[researchedCompanyList_Traditional]    Script Date: 12/7/2018 12:43:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Andrei Ioan
-- Create date: 05/12/2018
-- Description:	Retrieves the companies with any kind of uploaded involvement in the research table for PI Traditional
-- =============================================
ALTER PROCEDURE [dbo].[researchedCompanyList_Traditional]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT distinct CompanyId from pi.dbo.Framework where statusid>=4
END
--SP
USE [PI]
GO
/****** Object:  StoredProcedure [dbo].[researchedCompanyList_Solutions]    Script Date: 12/7/2018 12:43:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Andrei Ioan
-- Create date: 05/12/2018
-- Description:	Retrieves the companies with any kind of uploaded involvement in the research table for PI Solutions
-- =============================================
ALTER PROCEDURE [dbo].[researchedCompanyList_Solutions]
	-- Add the parameters for the stored procedure here

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT distinct CompanyId from pi.dbo.Framework_Solutions where statusid>=4
END

